﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using MySql.Data.MySqlClient;
using MySql.Data.Types;
using TAMS;

namespace TAMS
{
    public partial class oldCourses : System.Web.UI.Page
    {
        MySqlConnection conn;
        MySqlCommand sqlCommand;
        MySqlDataReader readVals;
        string strProvider = Resource1.databaseConnection;
        string type;
        string id;
        string enteredName;
        string currentYear;
        string currentSemester;


        protected void Page_Load(object sender, EventArgs e)
        {
            // Connect to the Database
            conn = new MySqlConnection(strProvider);
            conn.Open();
            string query;
            int month;

            currentYear = DateTime.Now.Year.ToString();
            month = Convert.ToInt32(DateTime.Now.Month.ToString());

            /*Dynamic Semester Parser ::G*/
            if (month >= 1 && month <= 5)
            {
                currentSemester = "SPRING";
            }
            else if (month >= 6 && month <= 9)
            {
                currentSemester = "SUMMER";
            }
            else if (month >= 9 && month <= 12)
            {
                currentSemester = "FALL";
            }


            // Get the type
            currentYear = Request.QueryString["year"];
            currentSemester = Request.QueryString["semester"];

            string getQuery;

            if (!IsPostBack)
            {
                getQuery = "SELECT SC.cid as 'Course ID', C.cname as 'Course Name', SC.sectionNo as 'Section' ,S.season as 'Semester', S.year as 'Year' " +
                              "FROM Course C, SectionConsists SC, Semester S " +
                              "WHERE SC.cid = C.cid AND SC.semId = S.semId " +
                              "GROUP BY SC.cid, SC.sectionNo, SC.secCorsID" +
                              " ORDER BY C.cid, S.season, S.year ";

                 Label9.Text = "All Courses";
                 string q1 = "SELECT SM.season,SM.year, COUNT(*) AS 'cnt' " +
                           " FROM Semester SM, SectionConsists SC " +
                            "WHERE SC.semId = SM.semId"+
                            "";

                 sqlCommand = new MySqlCommand(q1, conn);
                 readVals = sqlCommand.ExecuteReader();
                 readVals.Read();

                 Label10.Text = "Number of all  courses in system: " + readVals.GetInt32("cnt");
                 readVals.Close();
            }
            else
            {
                getQuery = "SELECT SC.cid as 'Course ID', C.cname as 'Course Name', SC.sectionNo as 'Section' ,S.season as 'Semester', S.year as 'Year' " +
                     "FROM Course C, SectionConsists SC, Semester S " +
                     "WHERE SC.cid = C.cid AND SC.semId = S.semId AND S.season=" + "'" + DropDownList2.SelectedValue + "'" + " AND S.year =" + "'" + DropDownList1.SelectedValue + "'" +
                     "GROUP BY SC.cid, SC.sectionNo";

                Label9.Text = "Courses of Semester:" + DropDownList1.SelectedValue + " Year: " + DropDownList2.SelectedValue;

                string q1 = "SELECT SM.season,SM.year, COUNT(*) AS 'cnt' " +
                            " FROM Semester SM, SectionConsists SC " +
                            "WHERE SC.semId = SM.semId AND SM.year='" + DropDownList1.SelectedValue + "' AND SM.season='" + DropDownList2.SelectedValue + "'" +
                            "";

                sqlCommand = new MySqlCommand(q1, conn);
                readVals = sqlCommand.ExecuteReader();
                readVals.Read();

                Label10.Text = "Number of courses in this semester: " + readVals.GetInt32("cnt");
                readVals.Close();

            }


            sqlCommand = new MySqlCommand(getQuery, conn);
            readVals = sqlCommand.ExecuteReader();

            GridView1.DataSource = readVals;
            GridView1.DataBind();
            readVals.Close();


        }

        protected void DropDownList2_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            Response.Redirect("oldCourses.aspx");
        }

        protected void details_click(object sender, EventArgs e)
        {
            Response.Redirect("courseDetails.aspx");
        }
    }
}